package com.dcncy.excel.exec;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddressList;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import com.dcncy.excel.exec.vo.Area;
import com.dcncy.excel.exec.vo.City;
import com.dcncy.excel.exec.vo.Province;
import com.dcncy.excel.exec.vo.Student;

//创建excel
public class CreateExcel {
    private static String filePathName = "d://test.xls";

    private static String DICT_SHEET_TEST = "测试模板";

    private static String DICT_SHEET_DATA = "数据源";

    private static String DICT_MNGNAME = "名称管理";

    public static void main(String[] args) {
        CreateExcel.createExcelFile();
    }

    /**
     * 创建并生成excel文档
     */
    @SuppressWarnings("checkstyle:WhitespaceAround")
    public static void createExcelFile() {
        List<Student> studens = GetData.getStudents(); // 测试数据，不影响功能
        try {
            FileOutputStream fileOutputStream = new FileOutputStream(new File(filePathName));
            HSSFWorkbook wb = writePorpData(); // 创建工作簿
            HSSFSheet sheet = wb.getSheet(DICT_SHEET_TEST); // 获取主工作表
            wb = getWorkbook(wb, studens.size());

            HSSFRow row = null;
            HSSFCell cell = null;
            sheet.setDefaultColumnWidth(28);
            row = sheet.createRow(0); // 新增标题行
            cell = row.createCell(0);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue("学生");
            cell = row.createCell(1);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue("省份");
            cell = row.createCell(2);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue("地区");
            cell = row.createCell(3);
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cell.setCellValue("城市");

            int i = 1;
            for (Student stu : studens) {
                row = sheet.createRow(i); // 新增一行
                cell = row.createCell(0);
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                cell.setCellValue(stu.getStudentName()); // 学生姓名
                cell = row.createCell(1);
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                cell.setCellValue(stu.getProvince()); // 省份
                cell = row.createCell(2);
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                cell.setCellValue(stu.getArea()); // 地区
                cell = row.createCell(3);
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                cell.setCellValue(stu.getCity()); // 城市
                i++;
            }

            sheet.setColumnWidth(0, 5000);// 设置列宽
            sheet.setColumnWidth(1, 5000);// 设置列宽
            sheet.setColumnWidth(2, 5000);// 设置列宽
            sheet.setColumnWidth(3, 5000);// 设置列宽

            wb.write(fileOutputStream); // 生成文档
            fileOutputStream.close();
        }
        catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 第一部分 将三个列表所有字段从数据库查询出，并生成名称管理器，存放至隐藏的sheet中
     */
    private static HSSFWorkbook writePorpData() {
        int index = 1;
        HSSFWorkbook wb = new HSSFWorkbook(); // Excel工作簿创建
        wb.createSheet(DICT_SHEET_TEST); // 创建主工作表sheet
        Sheet dictDataSheet = wb.createSheet(DICT_SHEET_DATA); // 创建数据源字段sheet
        List<Province> provinceList = GetData.getProvinces(); // 获取所有省份 --测试数据，不影响功能
        List<String> provinceNames = new ArrayList<String>(); // 1.存放所有省份的名称
        provinceNames.add("  "); // 使下拉框有置空的选择
        // 遍历每个省份
        for (Province province : provinceList) {
            String proName = province.getProvinceName(); // 获取每个省份名称
            provinceNames.add(proName);
            String provinceId = province.getProvinceId(); // 获取每个省份Id
            List<Area> areaList = GetData.getAreas(provinceId); // 获取每个地区 --测试数据，不影响功能
            List<String> areaNames = new ArrayList<String>(); // 2.存放所有地区名称
            areaNames.add("  "); // 使下拉框有置空的选择
            // 遍历每个地区
            for (Area area : areaList) {
                String areaName = area.getAreaName();
                areaNames.add(areaName);
                String areaId = area.getAreaId();
                List<City> cityList = GetData.getCities(areaId); // 获取每个城市 --测试数据，不影响功能
                List<String> cityNames = new ArrayList<String>(); // 3.存放所有城市名称
                cityNames.add("  "); // 使下拉框有置空的选择
                // 遍历每个城市
                for (City city : cityList) {
                    String cityName = city.getCityName();
                    cityNames.add(cityName);
                }
                cityNames.add(0, areaName);
                createRowData(dictDataSheet.createRow(index++), cityNames);// 3.创建二级属性行
                int i2 = 0;
                createExcelName(wb, cityNames.get(i2++), index, cityNames.size() - 1, true); // 3.二级属性行，指定名称管理
            }
            areaNames.add(0, proName);
            createRowData(dictDataSheet.createRow(index++), areaNames);// 2.创建一级属性行
            int i1 = 0;
            createExcelName(wb, areaNames.get(i1++), index, areaNames.size() - 1, true); // 2.一级属性行，指定名称管理
        }
        createRowData(dictDataSheet.createRow(0), provinceNames); // 1.创建首行属性行，写入数据
        createExcelName(wb, DICT_MNGNAME, 1, provinceNames.size() - 1, false); // 1.首行属性行，指定名称管理
        wb.setSheetHidden(wb.getSheetIndex(DICT_SHEET_DATA), true); // 设置隐藏的sheet
        return wb;
    }

    /**
     * 第二部分：2.4 设置数据的有效性，即下拉列表的生成
     */
    public static HSSFWorkbook getWorkbook(HSSFWorkbook wb, int size) {
        Sheet sheet = wb.getSheet(DICT_SHEET_TEST);
        DataValidation dataValidation = null;
        for (int x = 1; x <= size + 1; x++) {
            dataValidation = getDataValidation("IF($B$" + x + "=\"  \",\"  \",INDIRECT($B$" + x + "))", x, 3);
            sheet.addValidationData(dataValidation);

            dataValidation = getDataValidation("IF($C$" + x + "=\"  \",\"  \",INDIRECT($C$" + x + "))", x, 4);
            sheet.addValidationData(dataValidation);
        }
        return wb;
    }

    /**
     * 第二部分：2.5 数据验证
     */
    @SuppressWarnings("deprecation")
    private static DataValidation getDataValidation(String formulaString, int naturalRowIndex, int naturalColIndex) {
        // 设置数据有效性加载在哪个单元格上 四个参数：起始行、终止行、起始列、终止列
        int firstRow = naturalRowIndex - 1;
        int lastRow = naturalRowIndex - 1;
        int firstCol = naturalColIndex - 1;
        int lastCol = naturalColIndex - 1;
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        // 加载下拉列表
        DVConstraint constraint = DVConstraint.createFormulaListConstraint(formulaString);
        // 数据有效性对象
        DataValidation dataValidation = new HSSFDataValidation(regions, constraint);
        // 设置输入信息提示信息
        // dataValidation.createPromptBox("下拉提示", "请选择合适的值");
        // 设置输入错误提示信息
        dataValidation.createErrorBox("非法输入", "不允许输入，请选取下拉值！");
        return dataValidation;
    }

    /**
     * 第二部分：2.2 创建名称管理器的函数 每一行数据创建一个
     */
    private static void createExcelName(HSSFWorkbook workbook, String nameCode, int order, int size,
        boolean cascadeFlag) {
        Name name;
        name = workbook.createName();
        name.setNameName(nameCode);
        String cellString = DICT_SHEET_DATA + "!" + createExcelNameList(order, size, cascadeFlag);
        name.setRefersToFormula(cellString);
    }

    /**
     * 第二部分：2.1 创建隐藏sheet数据行的函数
     */
    private static void createRowData(Row curRow, List<String> dataList) {
        if (dataList != null && dataList.size() > 0) {
            int m = 0;
            for (String dataValue : dataList) {
                Cell dataCell = curRow.createCell(m++);
                dataCell.setCellValue(dataValue);
            }
        }
    }

    /**
     * 第二部分：2.3 名称数据行列计算表达式
     */
    private static String createExcelNameList(int order, int size, boolean cascadeFlag) {
        char start = 'A';
        if (cascadeFlag) {
            start = 'B';
            if (size <= 25) {
                char end = (char) (start + size - 1);
                return "$" + start + "$" + order + ":$" + end + "$" + order;
            }
            else {
                char endPrefix = 'A';
                char endSuffix = 'A';
                if ((size - 25) / 26 == 0 || size == 51) { // 26-51之间，包括边界
                    if ((size - 25) % 26 == 0) { // 边界值
                        endSuffix = (char) ('A' + 25);
                    }
                    else {
                        endSuffix = (char) ('A' + (size - 25) % 26 - 1);
                    }
                }
                else { // 51之上
                    if ((size - 25) % 26 == 0) {
                        endSuffix = (char) ('A' + 25);
                        endPrefix = (char) (endPrefix + (size - 25) / 26 - 1);
                    }
                    else {
                        endSuffix = (char) ('A' + (size - 25) % 26 - 1);
                        endPrefix = (char) (endPrefix + (size - 25) / 26);
                    }
                }
                return "$" + start + "$" + order + ":$" + endPrefix + endSuffix + "$" + order;
            }
        }
        else {
            if (size <= 26) {
                char end = (char) (start + size - 1);
                return "$" + start + "$" + order + ":$" + end + "$" + order;
            }
            else {
                char endPrefix = 'A';
                char endSuffix = 'A';
                if (size % 26 == 0) {
                    endSuffix = (char) ('A' + 25);
                    if (size > 52 && size / 26 > 0) {
                        endPrefix = (char) (endPrefix + size / 26 - 2);
                    }
                }
                else {
                    endSuffix = (char) ('A' + size % 26 - 1);
                    if (size > 52 && size / 26 > 0) {
                        endPrefix = (char) (endPrefix + size / 26 - 1);
                    }
                }
                return "$" + start + "$" + order + ":$" + endPrefix + endSuffix + "$" + order;
            }
        }
    }
}
